import pymysql

# 数据库连接配置
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "20234108@123",
    "charset": "utf8mb4"
}

# 创建数据库和表的 SQL 脚本
SQL_SCRIPT = """
-- 创建数据库
CREATE DATABASE IF NOT EXISTS DetectionChat DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE DetectionChat;

-- 创建 users 表
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
);

-- 创建 conversations 表
CREATE TABLE IF NOT EXISTS conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 创建 messages 表
CREATE TABLE IF NOT EXISTS messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    sender ENUM('user', 'ai') NOT NULL,
    content_type ENUM('text', 'image', 'mixed') NOT NULL,  -- 新增mixed类型支持文本+图片
    content TEXT,                    -- 文本内容
    oss_urls JSON,                   -- 多张图片URL，JSON数组格式
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);

-- 创建 result_groups 表（检测结果组，增加 user_id 字段）
CREATE TABLE IF NOT EXISTS result_groups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 创建 result_items 表（检测项，直接关联result_groups）
CREATE TABLE IF NOT EXISTS result_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    group_id INT NOT NULL,
    oss_url VARCHAR(255) NOT NULL,
    detection_info JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES result_groups(id)
);
"""

def execute_sql_script(sql_script, db_config):
    try:
        # 连接 MySQL 服务器
        connection = pymysql.connect(**db_config)
        cursor = connection.cursor()

        # 将 SQL 脚本按 `;` 拆分为单个语句
        sql_statements = sql_script.split(';')
        
        for statement in sql_statements:
            statement = statement.strip()
            if statement:  # 跳过空语句
                print(f"执行 SQL: {statement}")
                cursor.execute(statement)
        
        # 提交事务
        connection.commit()
        print("数据库操作完成！")
    except pymysql.MySQLError as e:
        print(f"错误: {e}")
    finally:
        # 关闭连接
        if connection:
            cursor.close()
            connection.close()

# 执行脚本
if __name__ == "__main__":
    execute_sql_script(SQL_SCRIPT, DB_CONFIG)
